package com.example.apidemo.dynamic_sql;


import com.example.apidemo.dynamic_sql.bean.AttributeItemEntity;
import com.example.apidemo.util.StringUtil;
import lombok.AllArgsConstructor;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
//import org.springframework.beans.factory.annotation.Autowired;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.*;

@Service
@AllArgsConstructor
public class DbStructureService {

    private final JdbcTemplate jdbcTemplate;

    private final String strP = "";//\"public\".//H2 不能加前缀


//    @Autowired
//    public DbStructureService(JdbcTemplate jdbcTemplate) {
//        this.jdbcTemplate = jdbcTemplate;
//    }


    ///创建表
    public boolean createTable(Long id, List<AttributeItemEntity> columns) {
        return createTable(AppConst.TABLE_PREFIX + id, columns);
    }

    //删除表格
    @Transactional(rollbackFor = Throwable.class)
    public boolean dropTables(List<Long> ids) {
        List<String> tableNames = new ArrayList<>();
        ids.forEach(id -> {
            tableNames.add(AppConst.TABLE_PREFIX + id);
        });
        return dropTable(tableNames);
    }

    ///添加列---待考证
    public boolean addColumns(Long id, List<AttributeItemEntity> columns) {
        return addColumns(AppConst.TABLE_PREFIX + id, columns);
    }

    //更新列---待考证
    public boolean updateColumns(Long id, List<AttributeItemEntity> columns, Map<Long, String> oldColumnMap) {
        return updateColumns(AppConst.TABLE_PREFIX + id, columns, oldColumnMap);
    }

    //删除列---待考证
    public boolean dropColumns(Long id, List<AttributeItemEntity> columns) {
        return dropColumns(AppConst.TABLE_PREFIX + id, columns);
    }

    private String columnType(String type, Integer length) {
        if ("varchar".equals(type)) {
            if (Objects.isNull(length) || length == 0) {
                length = 255;
            }
            return type + "(" + length + ")";
        }
        return type;
    }

    private boolean createTable(String tableName, List<AttributeItemEntity> columns) {
        StringBuffer sb = new StringBuffer();
        sb.append("CREATE TABLE IF NOT EXISTS " + strP + "\"" + tableName + "\" (\n");
        sb.append("\"id\" int8 NOT NULL,\n");
        columns.forEach(e -> {
            sb.append("\"" + e.getName() + "\" " + columnType(e.getType(), e.getLength()) + " " + isNullSql(e.getEnableNull()) + " " + isDefaultSql(e.getDefaultValue()) + ",\n");
        });
        sb.append("\"create_time\" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP\n");
        sb.append(");\n");
        sb.append("COMMENT ON COLUMN " + strP + "\"" + tableName + "\".\"id\" IS '主键';\n");
        columns.forEach(e -> {
            sb.append("COMMENT ON COLUMN " + strP + "\"" + tableName + "\".\"" + e.getName() + "\" IS '" + e.getAlias() + "';\n");
        });
        sb.append("COMMENT ON COLUMN " + strP + "\"" + tableName + "\".\"create_time\" IS '创建时间';\n");
        sb.append("ALTER TABLE " + strP + "\"" + tableName + "\" ADD CONSTRAINT \"" + tableName + "_pkey\" PRIMARY KEY (\"id\");");
        return execute(sb);
    }

    private boolean addColumns(String tableName, List<AttributeItemEntity> columns) {
        StringBuffer sb = new StringBuffer();
        columns.forEach(e -> {
            sb.append("ALTER TABLE " + strP + "\"" + tableName + "\"  ADD COLUMN \"" + e.getName() + "\" " + columnType(e.getType(), e.getLength()) + " " + isNullSql(e.getEnableNull()) + " " + isDefaultSql(e.getDefaultValue()) + ";\n");
            sb.append("COMMENT ON COLUMN " + strP + "\"" + tableName + "\".\"" + e.getName() + "\" IS '" + e.getAlias() + "';\n");
        });
        return execute(sb);
    }

    private boolean updateColumns(String tableName, List<AttributeItemEntity> columns, Map<Long, String> oldColumnMap) {
        StringBuffer sb = new StringBuffer();
        columns.forEach(e -> {
            String oldName = oldColumnMap.get(e.getId());
            if (oldName != null && !oldName.equals(e.getName())) {
                sb.append("ALTER TABLE " + strP + "\"" + tableName + "\"  RENAME COLUMN \"" + oldName + "\" TO \"" + e.getName() + "\";\n");
            }
            sb.append("COMMENT ON COLUMN " + strP + "\"" + tableName + "\".\"" + e.getName() + "\" IS '" + e.getAlias() + "';\n");
        });
        return execute(sb);
    }

    private boolean dropColumns(String tableName, List<AttributeItemEntity> columns) {
        StringBuffer sb = new StringBuffer();
        columns.forEach(e -> {
            sb.append("ALTER TABLE " + strP + "\"" + tableName + "\"  DROP COLUMN \"" + e.getName() + "\";\n");
        });
        return execute(sb);
    }


    ///删除多张表
    private boolean dropTable(List<String> tableNames) {
        StringBuffer sb = new StringBuffer();
        tableNames.forEach(tableName -> {
            sb.append("DROP TABLE IF EXISTS " + strP + "\"").append(tableName).append("\";");
        });
        return execute(sb);
    }

    //获取所有表名称
    public List<String> tableNames() {
        List<String> tableNames = new ArrayList<>();
        try {
            Connection getConnection = jdbcTemplate.getDataSource().getConnection();
            DatabaseMetaData metaData = getConnection.getMetaData();
            ResultSet rs = metaData.getTables(getConnection.getCatalog(), null, null, new String[]{"TABLE"});
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tableNames.add(tableName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tableNames;
    }


    private boolean execute(StringBuffer sb) {
        try {
            jdbcTemplate.execute(sb.toString());
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    private String isNullSql(Boolean boo) {
        if (boo) {
            return "NOT NULL";
        }
        return "";
    }

    private String isDefaultSql(Object obj) {
        if (Objects.nonNull(obj)) {
            if (obj instanceof String) {
                if (StringUtil.isEmpty(obj)) {
                    return "";
                }
                return "DEFAULT '" + obj + "'";
            }
            return "DEFAULT " + obj;
        }
        return "";
    }


    ///----

    ///判断表是否存在
    public boolean existenceTable(String tableName) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT count(*) FROM information_schema.tables WHERE table_name = ?;");
        //return execute(sb);
        int i = jdbcTemplate.queryForObject(sb.toString(), new Object[]{tableName}, Integer.class);
        System.out.println("打印：existenceTable->"+i);
        if (i == 1) {
            return true;
        }
        return false;
    }

    ///判断数据库是否存在
    public boolean existenceData(String dataName) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT count(*) SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?;");
        int i = jdbcTemplate.queryForObject(sb.toString(), new Object[]{dataName}, Integer.class);
        System.out.println("打印：existenceTable->"+i);
        if (i == 1) {
            return true;
        }
        return false;
    }

    /**
     * 删除指定的数据库表
     *
     * @param tableName 要删除的表名
     * @return 如果表被成功删除或表不存在返回true，否则返回false
     */
    public boolean dropTable(String tableName) {
        StringBuffer sb = new StringBuffer();
        sb.append("DROP TABLE IF EXISTS ");
        sb.append(tableName);
        sb.append(";\n");
        return execute(sb);

        //使用SQL语句删除表，如果表不存在，DROP TABLE IF EXISTS语句不会执行任何操作
//        String sql = "DROP TABLE IF EXISTS " + tableName;
//        try {
//            // 执行SQL语句
//            jdbcTemplate.execute(sql);
//            return true;
//        } catch (Exception e) {
//            // 处理异常，例如打印日志或转换为运行时异常
//            e.printStackTrace();
//            return false;
//        }
    }

}